

alter trigger tt_billbatch_bd
on dbo.tt_billbatch
instead of delete
as  
begin
  set nocount on
  declare @old_quantity decimal(18,4),  @depotid integer, @productid integer,@old_pid integer, @assqty decimal(18,4),
     @batchno varchar(200),
     @custom1 varchar(200), @custom2 varchar(200), @custom3 varchar(200),
     @length decimal(18,8), @width decimal(18,8), @height decimal(18,8)
     
  declare cr1 cursor local forward_only static optimistic for 
  select bquantity,depotid,productid,pid,assqty,batchcode,
       custom1, custom2, custom3, length, width, height from deleted
  open cr1
  fetch next from cr1 into @old_quantity,@depotid,@productid,@old_pid,@assqty,@batchno,
    @custom1, @custom2, @custom3, @length, @width, @height
  while @@fetch_status = 0
  begin   
    update tt_bookbatch set beginqty = beginqty - @old_quantity, quantity = quantity - @old_quantity,
      beginassqty = beginassqty - @assqty, assqty = assqty - @assqty
    where productid = @productid and depotid = @depotid and batchcode = @batchno
      and custom1=@custom1 and custom2=@custom2 and custom3=@custom3 and length=@length and width=@width and height=@height     
    delete from tt_billbatch where pid = @old_pid
    fetch next from cr1 into @old_quantity,@depotid,@productid,@old_pid,@assqty,@batchno,
    @custom1, @custom2, @custom3, @length, @width, @height
  end
  close cr1
  deallocate cr1
  set nocount off
end

